package com.lecture.java8.excel.dyr.l;

import com.alibaba.excel.EasyExcel;
import com.lecture.java8.excel.DemoDataListener;
import com.lecture.java8.excel.dyr.j.Model0729;

import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

public class Test0802 {

    public static void main(String[] args) {
        String dir = "D:\\study\\tmp\\0802\\";

        String path1 = dir + "1（最终数据EXCEL版）投资效率结果Chen模型至2022完整版.xlsx";
        DemoDataListener<Result0802_01> data1 = new DemoDataListener<>();
        EasyExcel.read(path1, Result0802_01.class,data1).sheet().doRead();
        //全要素生产率
        List<Result0802_01> resultList1 = data1.getDataList();
        Map<String,Result0802_01> resultMap1 = new HashMap<>();
        for (Result0802_01 result : resultList1) {
            resultMap1.put(result.getA()+"_"+result.getB(),result);
        }


        String path2 = dir + "22000-2022年30省环境规制数据（每千元工业增加值的工业污染治理投资额）.xlsx";
        DemoDataListener<Result0802_02> data2 = new DemoDataListener<>();
        EasyExcel.read(path2, Result0802_02.class,data2).sheet().doRead();
        //全要素生产率
        List<Result0802_02> resultList2 = data2.getDataList();
        Map<String,Result0802_02> resultMap2 = new HashMap<>();
        for (Result0802_02 result : resultList2) {
            resultMap2.put(result.getB()+"_"+result.getC(),result);
        }


        String path3 = dir + "4报刊财经新闻量化统计年度数据（1994-2023）.xls";
        DemoDataListener<Result0802_03> data3 = new DemoDataListener<>();
        EasyExcel.read(path3, Result0802_03.class,data3).sheet().doRead();
        //全要素生产率
        List<Result0802_03> resultList3 = data3.getDataList();
        Map<String,Result0802_03> resultMap3 = new HashMap<>();
        for (Result0802_03 result : resultList3) {
            resultMap3.put(result.getA()+"_"+result.getB(),result);
        }



        String path4 = dir + "5环保投资+绿色投资(1).xlsx";
        DemoDataListener<Result0802_04> data4 = new DemoDataListener<>();
        EasyExcel.read(path4, Result0802_04.class,data4).sheet().doRead();
        //全要素生产率
        List<Result0802_04> resultList4 = data4.getDataList();
        Map<String,Result0802_04> resultMap4 = new HashMap<>();
        for (Result0802_04 result : resultList4) {
            resultMap4.put(result.getD()+"_"+result.getA(),result);
        }


        String path5 = dir + "6技术创新.xlsx";
        DemoDataListener<Result0802_05> data5 = new DemoDataListener<>();
        EasyExcel.read(path5, Result0802_05.class,data5).sheet().doRead();
        //全要素生产率
        List<Result0802_05> resultList5 = data5.getDataList();
        Map<String,Result0802_05> resultMap5 = new HashMap<>();
        for (Result0802_05 result : resultList5) {

            resultMap5.put(result.getA()+"_"+result.getB().substring(0,4),result);
        }



        String path6 = dir + "7上市公司绿色专利申请情况.xlsx";
        DemoDataListener<Result0802_06> data6 = new DemoDataListener<>();
        EasyExcel.read(path6, Result0802_06.class,data6).sheet().doRead();
        //全要素生产率
        List<Result0802_06> resultList6 = data6.getDataList();
        Map<String,Result0802_06> resultMap6 = new HashMap<>();
        for (Result0802_06 result : resultList6) {
            resultMap6.put(result.getA()+"_"+result.getB(),result);
        }


        String path7 = dir + "8上市公司专利获得情况（原始）.xlsx";
        DemoDataListener<Result0802_07> data7 = new DemoDataListener<>();
        EasyExcel.read(path7, Result0802_07.class,data7).sheet().doRead();
        //全要素生产率
        List<Result0802_07> resultList7 = data7.getDataList();
        Map<String,Result0802_07> resultMap7 = new HashMap<>();
        for (Result0802_07 result : resultList7) {
            resultMap7.put(result.getA()+"_"+result.getB(),result);
        }



        String path8 = dir + "9投资效率结果Biddle模型至2022完整版.xlsx";
        DemoDataListener<Result0802_08> data8 = new DemoDataListener<>();
        EasyExcel.read(path8, Result0802_08.class,data8).sheet().doRead();
        //全要素生产率
        List<Result0802_08> resultList8 = data8.getDataList();
        Map<String,Result0802_08> resultMap8 = new HashMap<>();
        for (Result0802_08 result : resultList8) {
            resultMap8.put(result.getC()+"_"+result.getD(),result);
        }



        String path9 = dir + "10网络新闻量化统计年度数据（1994-2023）.xls";
        DemoDataListener<Result0802_09> data9 = new DemoDataListener<>();
        EasyExcel.read(path9, Result0802_09.class,data9).sheet().doRead();
        //全要素生产率
        List<Result0802_09> resultList9 = data9.getDataList();
        Map<String,Result0802_09> resultMap9 = new HashMap<>();
        for (Result0802_09 result : resultList9) {
            resultMap9.put(result.getA()+"_"+result.getB(),result);
        }



        Set<String> codeList = new HashSet<>();

        String modelPath = dir + "2013年带行业变量的.xlsx";
        DemoDataListener<Model0802> modelData = new DemoDataListener<>();
        EasyExcel.read(modelPath, Model0802.class,modelData).sheet().doRead();
        //全要素生产率
        List<Model0802> modelList = modelData.getDataList();
        for (Model0802 model : modelList) {
            Result0802_01 r1 = resultMap1.get(model.getA() + "_" + model.getC());
            if (r1 != null) {
                model.setAv(r1.getG());
                model.setAw(r1.getH());
                model.setAz(r1.getI());

            }
            String pro = model.getU().replace("市","")
                    .replace("省","")
                    .replace("壮族自治区","")
                    .replace("回族自治区","")
                    .replace("维吾尔自治区","")
                    .replace("自治区","");

            Result0802_02 r2 = resultMap2.get(model.getC() + "_" + pro);
            if (r2 != null) {
                model.setAy(r2.getD());
                model.setAz(r2.getE());
                model.setBa(r2.getF());
            } else {
                codeList.add(model.getA());
            }
            Result0802_03 r3 = resultMap3.get(model.getA() + "_" + model.getC());
            if (r3 != null) {
                model.setBb(r3.getC());
                model.setBc(r3.getD());
                model.setBd(r3.getE());
                model.setBe(r3.getF());
                model.setBf(r3.getG());
            }
            Result0802_04 r4 = resultMap4.get(model.getA() + "_" + model.getC());
            if (r4 != null) {
                model.setBg(r4.getB());
                model.setBh(r4.getC());
            }
            Result0802_05 r5 = resultMap5.get(model.getA() + "_" + model.getC());
            if (r5 != null) {
                model.setBi(r5.getC());
                model.setBj(r5.getD());
                model.setBk(r5.getE());
            }
            Result0802_06 r6 = resultMap6.get(model.getA() + "_" + model.getC());
            if (r6 != null) {
                model.setBl(r6.getC());
                model.setBm(r6.getD());

            }
            Result0802_07 r7 = resultMap7.get(model.getA() + "_" + model.getC());
            if (r7 != null) {
                model.setBn(r7.getC());
                model.setBo(r7.getD());
                model.setBp(r7.getE());
            }
            Result0802_08 r8 = resultMap8.get(model.getA() + "_" + model.getC());
            if (r8 != null) {
                model.setBq(r8.getE());
                model.setBr(r8.getF());
                model.setBs(r8.getG());
                model.setBt(r8.getH());
            }
            Result0802_09 r9 = resultMap9.get(model.getA() + "_" + model.getC());
            if (r9 != null) {
                model.setBu(r9.getC());
                model.setBv(r9.getD());
                model.setBw(r9.getE());
                model.setBx(r9.getF());
                model.setBy(r9.getG());
            }


        }

        System.out.println(Arrays.toString(codeList.toArray()));

        EasyExcel.write( dir + "0802-3结果.xlsx", Model0802.class)
                .sheet("a").doWrite(modelList);

    }

    public static List<Model0729> filterCompleteYears(List<Model0729> list, int startYear, int endYear) {
        Set<Integer> requiredYears = IntStream.rangeClosed(startYear, endYear)
                .boxed()
                .collect(Collectors.toSet());

        // Group by code and filter
        Map<String, Set<Integer>> codeToYearsMap = list.stream()
                .collect(Collectors.groupingBy(
                        Model0729::getA,
                        Collectors.mapping(Model0729::getC, Collectors.toSet())
                ));


        Set<String> validCodes = codeToYearsMap.entrySet().stream()
                .filter(entry -> entry.getValue().equals(requiredYears))
                .map(Map.Entry::getKey)
                .collect(Collectors.toSet());

        // Filter original list by valid codes
        return list.stream()
                .filter(obj -> validCodes.contains(obj.getA()))
                .collect(Collectors.toList());
    }
}
